--    Program: 	  CAPCMIMP_CE_STATEMENT_LINES_INTERFACE.sql
--    Description:  Import CM CE STATEMENT LINES Interface information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Andy Mak 
--    Date:         8-May-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg			varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_count                    number;

                    		                 		
   cursor cur_1 is
      select * from CRST_CE_STMT_LINES_INTERFACE;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_1 loop   	
     l_err_msg_tmp := '';

     l_count := 0;

     begin
       SELECT count(1)
       INTO   l_count
       FROM   CRST_CE_STMT_HEADERS_INT_ALL
       WHERE  statement_number = cur.statement_number
	 and bank_account_num = cur.bank_account_num;
     exception
       when others then
         l_err_msg_tmp := 'Bank Account Number : '||cur.bank_account_num||' Statement Number : ' || cur.statement_number || ' not found';
     end;

   if l_count > 0 then
     begin
       -- import data
       insert into CE_STATEMENT_LINES_INTERFACE
       (
  		BANK_ACCOUNT_NUM         ,
  		STATEMENT_NUMBER         ,
  		LINE_NUMBER              ,
  		TRX_DATE                 ,
  		TRX_CODE                 ,
  		EFFECTIVE_DATE           ,
  		TRX_TEXT                 ,
  		INVOICE_TEXT             ,
  		BANK_ACCOUNT_TEXT        ,
  		AMOUNT                   ,
  		CURRENCY_CODE            ,
  		USER_EXCHANGE_RATE_TYPE  ,
  		EXCHANGE_RATE_DATE       ,
  		EXCHANGE_RATE            ,
  		ORIGINAL_AMOUNT          ,
  		CHARGES_AMOUNT           ,
  		BANK_TRX_NUMBER          ,
  		CUSTOMER_TEXT            ,
  		CREATED_BY               ,
  		CREATION_DATE            ,
  		LAST_UPDATED_BY          ,
  		LAST_UPDATE_DATE         ,
  		ATTRIBUTE_CATEGORY       ,
  		ATTRIBUTE1               ,
  		ATTRIBUTE2               ,
  		ATTRIBUTE3               ,
  		ATTRIBUTE4               ,
  		ATTRIBUTE5               ,
  		ATTRIBUTE6               ,
  		ATTRIBUTE7               ,
  		ATTRIBUTE8               ,
  		ATTRIBUTE9               ,
  		ATTRIBUTE10              ,
  		ATTRIBUTE11              ,
  		ATTRIBUTE12              ,
  		ATTRIBUTE13              ,
  		ATTRIBUTE14              ,
 		ATTRIBUTE15               
       )
       values
       (   
  		cur.BANK_ACCOUNT_NUM         ,
  		cur.STATEMENT_NUMBER         ,
  		cur.LINE_NUMBER              ,
  		cur.TRX_DATE                 ,
  		cur.TRX_CODE                 ,
  		cur.EFFECTIVE_DATE           ,
  		cur.TRX_TEXT                 ,
  		cur.INVOICE_TEXT             ,
  		cur.BANK_ACCOUNT_TEXT        ,
  		cur.AMOUNT                   ,
  		cur.CURRENCY_CODE            ,
  		cur.USER_EXCHANGE_RATE_TYPE  ,
  		cur.EXCHANGE_RATE_DATE       ,
  		cur.EXCHANGE_RATE            ,
  		cur.ORIGINAL_AMOUNT          ,
  		cur.CHARGES_AMOUNT           ,
  		cur.BANK_TRX_NUMBER          ,
  		cur.CUSTOMER_TEXT            ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
  		cur.CREATION_DATE            ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
  		cur.LAST_UPDATE_DATE         ,
  		cur.ATTRIBUTE_CATEGORY       ,
  		cur.ATTRIBUTE1               ,
  		cur.ATTRIBUTE2               ,
  		cur.ATTRIBUTE3               ,
  		cur.ATTRIBUTE4               ,
  		cur.ATTRIBUTE5               ,
  		cur.ATTRIBUTE6               ,
  		cur.ATTRIBUTE7               ,
  		cur.ATTRIBUTE8               ,
  		cur.ATTRIBUTE9               ,
  		cur.ATTRIBUTE10              ,
  		cur.ATTRIBUTE11              ,
  		cur.ATTRIBUTE12              ,
  		cur.ATTRIBUTE13              ,
  		cur.ATTRIBUTE14              ,
 		cur.ATTRIBUTE15               
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
   end if;
  
     if (l_err_msg_tmp = '' or l_err_msg_tmp is null) then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   if l_err_msg is null then
       l_err_msg := 'No Error';
   end if;
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPCMIMP_CE_STATEMENT_LINES_INTERFACE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/
exit
